const { Sequelize } = require('sequelize');

// 数据库连接配置（与项目配置保持一致）
const sequelize = new Sequelize('sso_system', 'root', '12345678', {
  host: 'localhost',
  dialect: 'mysql',
  logging: console.log,
  timezone: '+08:00'
});

// 创建数据库表的SQL
const createTablesSQL = `
-- 文档表
CREATE TABLE IF NOT EXISTS documents (
  id VARCHAR(36) PRIMARY KEY COMMENT '文档ID，UUID格式',
  title VARCHAR(255) NOT NULL COMMENT '文档标题',
  content LONGTEXT NULL COMMENT '文档内容，JSON格式存储BlockNote内容',
  yjs_state LONGBLOB NULL COMMENT 'Yjs文档状态，二进制格式',
  owner_id BIGINT NOT NULL COMMENT '文档拥有者ID，关联users表',
  status TINYINT DEFAULT 1 COMMENT '文档状态：1-正常，0-删除，2-草稿',
  is_public BOOLEAN DEFAULT FALSE COMMENT '是否公开文档',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  last_sync_at DATETIME NULL COMMENT '最后同步时间',

  INDEX idx_owner_id (owner_id),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at),
  INDEX idx_is_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文档基础信息表';

-- 文档协作关系表（去除邀请状态，默认同意）
CREATE TABLE IF NOT EXISTS document_collaborations (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '协作关系ID，主键自增',
  document_id VARCHAR(36) NOT NULL COMMENT '文档ID，关联documents表',
  user_id BIGINT NOT NULL COMMENT '协作用户ID，关联users表',
  permission ENUM('read', 'write', 'admin') NOT NULL DEFAULT 'write' COMMENT '权限级别：read-只读，write-编辑，admin-管理员',
  invited_by BIGINT NOT NULL COMMENT '邀请人ID，关联users表',
  invited_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '邀请时间',
  responded_at DATETIME NULL COMMENT '响应时间',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

  INDEX idx_document_id (document_id),
  INDEX idx_user_id (user_id),
  UNIQUE KEY idx_document_user (document_id, user_id),

  FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文档协作关系表';

-- Yjs文档持久化表
CREATE TABLE IF NOT EXISTS yjs_documents (
  id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 'Yjs文档ID，主键自增',
  document_id VARCHAR(36) NOT NULL UNIQUE COMMENT '关联的文档ID',
  room_name VARCHAR(255) NOT NULL COMMENT 'Yjs房间名称',
  yjs_state LONGBLOB NOT NULL COMMENT 'Yjs文档完整状态',
  yjs_updates JSON NULL COMMENT 'Yjs增量更新记录，JSON数组格式',
  last_updated DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',
  version INT DEFAULT 1 COMMENT '文档版本号',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

  UNIQUE INDEX idx_document_id (document_id),
  INDEX idx_room_name (room_name),
  INDEX idx_last_updated (last_updated),

  FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Yjs文档持久化表';
`;

async function initDatabase() {
  try {
    console.log('开始初始化文档相关数据库表...');

    // 测试连接
    await sequelize.authenticate();
    console.log('数据库连接成功');

    // 创建表（逐条执行，避免多语句执行限制）
    const statements = createTablesSQL
      .split(/;\s*\n/) // 以分号+换行分割
      .map(s => s.trim())
      .filter(s => s && !s.startsWith('--'));

    for (const sql of statements) {
      await sequelize.query(sql);
    }
    console.log('文档相关数据库表创建成功');

    // 迁移：移除旧版协作关系的 status 字段与索引（若存在）
    await migrateCollaborationSchema();

    // 创建测试数据
    await createTestData();

    console.log('文档数据库初始化完成');
  } catch (error) {
    console.error('文档数据库初始化失败:', error);
    process.exit(1);
  } finally {
    await sequelize.close();
  }
}

// 尝试移除旧的 status 字段与索引
async function migrateCollaborationSchema() {
  try {
    await sequelize.query("ALTER TABLE document_collaborations DROP INDEX idx_status");
    console.log('已删除 idx_status 索引');
  } catch (e) {
    // 索引不存在时忽略
  }
  try {
    await sequelize.query("ALTER TABLE document_collaborations DROP COLUMN status");
    console.log('已删除 status 字段');
  } catch (e) {
    // 字段不存在时忽略
  }
}

async function createTestData() {
  try {
    console.log('开始创建测试数据...');

    // 检查是否已存在测试用户
    const [users] = await sequelize.query('SELECT id, username FROM users LIMIT 2');

    if (users.length < 2) {
      console.log('用户数据不足，请先运行用户表初始化');
      return;
    }

    const user1 = users[0];
    const user2 = users[1];

    // 创建测试文档
    const documentId = 'doc-test-' + Date.now();
    const insertDocumentSQL = `
      INSERT INTO documents (id, title, content, owner_id, status, is_public, created_at, updated_at)
      VALUES (?, ?, ?, ?, 1, FALSE, NOW(), NOW())
      ON DUPLICATE KEY UPDATE title = VALUES(title), updated_at = NOW()
    `;

    const documentContent = JSON.stringify([
      {
        type: "paragraph",
        content: "这是一个测试文档，用于验证数据库结构。"
      },
      {
        type: "paragraph",
        content: "支持多种块类型和协同编辑功能。"
      }
    ]);

    await sequelize.query(insertDocumentSQL, {
      replacements: [documentId, '测试文档', documentContent, user1.id]
    });

    console.log(`创建测试文档: ${documentId}, 拥有者: ${user1.username}`);

    // 创建协作关系
    const insertCollaborationSQL = `
      INSERT INTO document_collaborations (
        document_id, user_id, permission, invited_by, invited_at, created_at, updated_at
      )
      VALUES (?, ?, 'write', ?, NOW(), NOW(), NOW())
      ON DUPLICATE KEY UPDATE permission = VALUES(permission), updated_at = NOW()
    `;

    await sequelize.query(insertCollaborationSQL, {
      replacements: [documentId, user2.id, user1.id]
    });

    console.log(`添加协作者: ${user2.username} 到文档 ${documentId}`);

    // 创建Yjs文档记录
    const roomName = `doc-${documentId}`;
    const mockYjsState = Buffer.from('mock-yjs-state-data'); // 模拟Yjs状态数据

    const insertYjsDocSQL = `
      INSERT INTO yjs_documents (document_id, room_name, yjs_state, version, last_updated, created_at, updated_at)
      VALUES (?, ?, ?, 1, NOW(), NOW(), NOW())
      ON DUPLICATE KEY UPDATE version = version + 1, last_updated = NOW(), updated_at = NOW()
    `;

    await sequelize.query(insertYjsDocSQL, {
      replacements: [documentId, roomName, mockYjsState]
    });

    console.log(`创建Yjs文档记录: 房间 ${roomName}`);

    console.log('测试数据创建完成');
  } catch (error) {
    console.error('创建测试数据失败:', error);
    throw error;
  }
}

// 执行初始化
if (require.main === module) {
  initDatabase();
}

module.exports = { initDatabase };
